﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;

namespace Jurassic.LibraryManage.DataAccess
{
    class DbHelper
    {
        //获取数据库引用类库类型
        private static string dbProviderName = "System.Data.SqlClient";

        //获取数据库驱动
        private static string dbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        private DbConnection connection;//数据库连接类实例

        //创建数据库连接的函数
        public DbHelper(string connectionString)
        {
            this.connection = CreateConnection(connectionString);
        }

        //通过工厂创建数据库的链接，构造函数重载调用
        public static DbConnection CreateConnection(string connectionString)
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);//这里DbProviderFactory是一个对数据库进行操作的类，它可以生产各种关于数据库的对象
            DbConnection dbConn = dbFactory.CreateConnection();
            dbConn.ConnectionString = connectionString;//这里将用于打开数据库的字符串传给dbConn，再返回dbConn，这样就可以创建一个数据库的链接了
            return dbConn;
        }

        //设置数据库操作源为存储过程
        public DbCommand GetStoredProcCommand(string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = System.Data.CommandType.StoredProcedure;//把dbCommand的命令类型设置为存储过程，以备调用数据库的存储过程之需
            return dbCommand;
        }

        //设置数据库操作源为SQL语句
        public DbCommand GetSqlStringCommand(string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = System.Data.CommandType.Text;//把dbCommand的命令类型设置为SQL语句，以备调用数据库的SQL语句之需
            return dbCommand;
        }

        #region 添加参数
        //增加参数，添加参数集合
        public void AddParameterCollection(DbCommand dbCommand, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                dbCommand.Parameters.Add(dbParameter);
            }
        }

        //添加参数集合
        public void AddSqlParameterCollection(DbCommand dbCommand, SqlParameter SqlPara)
        {
            dbCommand.Parameters.Add(SqlPara);
        }

        //添加只可输出的参数进去参数集合
        public void AddOutParameterCollection(DbCommand dbCommand, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = dbCommand.CreateParameter();//实例化一个DbParameter类，表示DbCommand的参数
            dbParameter.DbType = dbType;//设置参数的DbType
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;//设置参数是只可输出的
            dbCommand.Parameters.Add(dbParameter);//添加参数
        }

        //添加只可输入的参数进去参数集合
        public void AddInParameterCollection(DbCommand dbCommand, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = dbCommand.CreateParameter();//实例化一个DbParameter类，表示DbCommand的参数
            dbParameter.DbType = dbType;//设置参数的DbType
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;//设置参数是只可输出的
            dbCommand.Parameters.Add(dbParameter);//添加参数
        }

        //设置输入存储过程的参数集合
        public void AddInParameter(DbCommand dbCommand, params SqlParameter[] values)
        {
            DbParameter dbParameter = dbCommand.CreateParameter();
            dbParameter.Direction = ParameterDirection.Input;
            dbCommand.Parameters.Add(dbParameter);
        }

        //设置输出存储过程的参数集合
        public void AddReturnParameter(DbCommand dbCommand, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = dbCommand.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            dbCommand.Parameters.Add(dbParameter);
        }

        public DbParameter GetParameter(DbCommand dbCommand, string parameterName)
        {
            return dbCommand.Parameters[parameterName];
        }

        #endregion

        #region 执行，关于Access的

        //直接执行SQL语句，得到第一行第一行的查询结果，在做网站用到Access是使用
        public static object ExecuteScalarWeb(string sqlText, params OleDbParameter[] param)
        {
            object obj = null;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = sqlText;
                cmd.Connection = conn;
                cmd.Parameters.AddRange(param);
                conn.Open();
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            return obj;
        }

        //直接执行SQL语句，得到第一行第一行的查询结果，在做网站用到Access是使用
        public static object ExecuteScalar(string sqlCommand)
        {
            object obj = null;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbCommand cmd = new OleDbCommand(sqlCommand, conn);
                conn.Open();
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            return obj;
        }

        //执行直接读取SQL语句，返回影响的行数
        public static int ExecuteNonQuery(string sqlCommand)
        {
            int ret = -1;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbCommand cmd = new OleDbCommand(sqlCommand, conn);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return ret;
        }

        //执行直接读取SQL语句，返回影响的行数
        public static int ExecuteNonQuery(string sqlCommand, params OleDbParameter[] param)
        {
            int ret = -1;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbCommand cmd = new OleDbCommand(sqlCommand, conn);
                cmd.Parameters.AddRange(param);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return ret;
        }

        //执行直接读取SQL语句，返回一个数据表的操作
        public static DataTable ExecuteDataTable(string sqlCommand)
        {
            DataTable dt = null;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbDataAdapter sda = new OleDbDataAdapter();
                sda.SelectCommand = new OleDbCommand(sqlCommand, conn);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }

        //执行直接读取SQL语句，返回一个数据表的操作
        public static DataTable ExecuteDataTable(string sqlCommand, params OleDbParameter[] param)
        {
            DataTable dt = null;
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = dbConnectionString;
                OleDbDataAdapter sda = new OleDbDataAdapter();
                sda.SelectCommand = new OleDbCommand(sqlCommand, conn); sda.SelectCommand.Parameters.AddRange(param);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }
        #endregion

        #region 执行，关于SQL的

        //一个返回搜索结果第一行第一列的事务
        public object ExecuteScalar(DbCommand dbCommand)
        {
            dbCommand.Connection.Open();
            object ret = dbCommand.ExecuteScalar();//返回查询结果的第一行第一列的值
            dbCommand.Connection.Close();
            return ret;
        }

        //执行返回搜索结果第一行第一列的结果
        public object ExecuteScalar(DbCommand dbCommand, params SqlParameter[] param)
        {
            AddInParameter(dbCommand, param);
            dbCommand.Connection.Open();
            object ret = dbCommand.ExecuteScalar();//返回查询结果的第一行第一列的值
            dbCommand.Connection.Close();
            return ret;
        }

        //执行直接读取SQL语句，返回查询结果第一行第一列的数据
        public static object ExecuteScalar(string sqlCommand)
        {
            object obj = null;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = dbConnectionString;
                SqlCommand cmd = new SqlCommand(sqlCommand, conn);
                conn.Open();
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            return obj;
        }

        //直接读取SQL语句，返回第一行第一列的结果
        public static object ExecuteScalar(string sqlCommand, params SqlParameter[] param)
        {

            {
                object obj = null;
                using (SqlConnection conn = new SqlConnection())
                {
                    conn.ConnectionString = dbConnectionString;
                    SqlCommand cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.AddRange(param);//将param加到cmd的参数数组的末尾
                    conn.Open();
                    obj = cmd.ExecuteScalar();
                    conn.Close();
                }
                return obj;
            }
        }

        //执行一个返回数据缓存的操作
        public DataSet ExecuteDataSet(DbCommand dbCommand)
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = dbCommand;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        //执行一个返回数据缓存的操作
        public DataSet ExecuteDataSet(DbCommand dbCommand, params SqlParameter[] param)
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter();
            AddInParameter(dbCommand, param);
            dbDataAdapter.SelectCommand = dbCommand;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        //执行返回一个数据表的操作
        public DataTable ExecuteDataTable(DbCommand dbCommand)
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = dbCommand;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        //执行返回一个数据表的操作
        public DataTable ExecuteDataTable(DbCommand dbCommand, params SqlParameter[] param)
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter();
            AddInParameter(dbCommand, param);
            dbDataAdapter.SelectCommand = dbCommand;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        //执行直接读取SQL语句，返回一个数据表的操作
        public static DataTable ExecuteDataTable(string sqlCommand)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = dbConnectionString;
                SqlDataAdapter sda = new SqlDataAdapter(sqlCommand, conn);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }

        //执行直接读取SQL语句，返回一个数据表的操作
        public static DataTable ExecuteDataTable(string sqlCommand, params SqlParameter[] param)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = dbConnectionString;
                SqlDataAdapter sda = new SqlDataAdapter(sqlCommand, conn); sda.SelectCommand.Parameters.AddRange(param);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }



        //直接读取SQL语句，读取数据
        public static SqlDataReader ExecuteDataReader(string sqlCommand, params SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = dbConnectionString;
            SqlCommand cmd = new SqlCommand(sqlCommand, conn);
            cmd.Parameters.AddRange(param);
            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }

        //直接读取SQL语句，读取数据
        public static SqlDataReader ExecuteDataReader(string sqlCommand)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = dbConnectionString;
            SqlCommand cmd = new SqlCommand(sqlCommand, conn);
            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }

        //执行只读数据操作
        public DbDataReader ExecuteReader(DbCommand dbCommand)
        {
            dbCommand.Connection.Open();
            DbDataReader reader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection
);
            reader.Close();
            return reader;
        }

        //执行只读数据操作
        public DbDataReader ExecuteReader(DbCommand dbCommand, params SqlParameter[] param)
        {
            AddInParameter(dbCommand, param);
            dbCommand.Connection.Open();
            DbDataReader reader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection
);
            reader.Close();
            return reader;
        }

        //执行读SQL语句，返回影响的行熟的操作
        public int ExecuteNonQuery(DbCommand dbCommand)
        {
            dbCommand.Connection.Open();
            int ret = dbCommand.ExecuteNonQuery();
            dbCommand.Connection.Close();
            return ret;
        }

        //执行返回SQL语句影响行数
        public int ExecuteNonQuery(DbCommand dbCommand, params SqlParameter[] param)
        {
            AddInParameter(dbCommand, param);
            dbCommand.Connection.Open();
            int ret = dbCommand.ExecuteNonQuery();//这里ExecuteNonQuery()方法返回执行SQL语句后影响的行数
            dbCommand.Connection.Close();
            return ret;
        }

        //执行直接读取SQL语句，返回影响的行数
        public static int ExecuteNonQuery(string sqlCommand)
        {
            int ret = -1;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = dbConnectionString;
                SqlCommand cmd = new SqlCommand(sqlCommand, conn);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return ret;
        }

        //执行直接读取SQL语句，返回影响的行数
        public static int ExecuteNonQuery(string sqlCommand, params SqlParameter[] param)
        {
            int ret = -1;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = dbConnectionString;
                SqlCommand cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.AddRange(param);
                conn.Open();
                ret = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return ret;
        }

        #endregion

        #region 事物处理T_SQL

        //一个返回数据缓存事务
        public DataSet ExecuteDataSet(DbCommand dbCommand, Trans t)
        {
            dbCommand.Connection = t.DbConnection;
            dbCommand.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = dbCommand;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        //一个返回数据表的事务
        public DataTable ExecuteDataTable(DbCommand dbCommand, Trans t)
        {
            dbCommand.Connection = t.DbConnection;
            dbCommand.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = dbCommand;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }
        //从数据源读取一行数据的事务
        public DbDataReader ExecuteReader(DbCommand dbCommand, Trans t)
        {
            dbCommand.Connection.Close();
            dbCommand.Connection = t.DbConnection;
            dbCommand.Transaction = t.DbTrans;
            DbDataReader reader = dbCommand.ExecuteReader();
            DataTable dt = new DataTable();
            return reader;
        }

        //返回SQL语句执行后影响的行数的事务
        public int ExecuteNonQuery(DbCommand dbCommand, Trans t, params SqlParameter[] param)
        {
            AddInParameter(dbCommand, param);
            dbCommand.Connection.Close();
            dbCommand.Connection = t.DbConnection;
            dbCommand.Transaction = t.DbTrans;
            int ret = dbCommand.ExecuteNonQuery();
            return ret;
        }

        //从查询的数据中返回第一行第一列的事务
        public object ExecuteScalar(DbCommand dbCommand, Trans t)
        {
            dbCommand.Connection.Close();
            dbCommand.Connection = t.DbConnection;
            dbCommand.Transaction = t.DbTrans;
            object ret = dbCommand.ExecuteScalar();
            return ret;
        }

        public class Trans : IDisposable//类Trans继承于释放分配的资源的类IDisposable
        {
            private DbConnection conn;//定义一个数据库连接的类的实例
            private DbTransaction dbTrans;//定义一个事务基类的实例

            public DbConnection DbConnection//输出一个数据库连接的函数
            {
                get { return this.conn; }
            }

            public DbTransaction DbTrans//输出一个事务的函数
            {
                get { return this.dbTrans; }
            }

            public Trans()
            {
                conn.Open();//打开连接数据库的实例
                dbTrans = conn.BeginTransaction();//事务实例为开始一个数据库事务
            }

            public Trans(string connectionString)
            {
                conn = DbHelper.CreateConnection(connectionString);//得到一个数据库额连接
                conn.Open();//打开这个连接
                dbTrans = conn.BeginTransaction();//事务实例为开始一个数据库事务
            }

            public void Commit()//提交数据库事务，再关闭数据库连接
            {
                dbTrans.Commit();
                this.Close();
            }

            public void RollBack()//从挂起状态回滚事务（说实话我不知道这是什么意思）,然后关闭数据库连接
            {
                dbTrans.Rollback();
                this.Close();
            }

            public void Dispose()//也就是关闭当前的数据库连接，不知道这个函数有什么作用？？？？
            {
                this.Close();
            }

            public void Close()//如果当前数据库连接实例的链接状态为open，就关闭这个数据库连接
            {
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        #endregion
    }
}
